package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.tag.vo.datatable.SortDirection;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.service.SystemService;

import yongxin.entity.annealer.AnnealerEntity;
import yongxin.entity.check.ContractEntity;
import yongxin.entity.check.ContractProcutEntity;
import yongxin.entity.processreport.ProcessReportEntity;
import yongxin.entity.temp_record.TempRecordEntity;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 成品入库查詢
 * @author zhangdaihao
 * @date 2015-02-05 14:03:13
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/stockBill2Controller")
public class StockBill2Controller extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(StockBill2Controller.class);

	@Autowired
	private SystemService systemService;

	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 成品入库 页面跳转
	 * 
	 * @return
	 */
	@RequestMapping(params = "stockBill2List")
	public ModelAndView stockBill2List(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/stockBill2List");

	}

	/**
	 * easyui 成品入库
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "c_datagrid")
	public void c_datagrid(ProcessReportEntity processReport, HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{


		String sql = " select  v1.Fdate AS FDate,v1.FBillNo AS FBillNo,t7.FName AS FDCStockName, ";
		sql += " t12.FShortNumber AS FShortNumber,t12.FNumber AS FFullNumber,t12.Fname AS FItemName,t12.Fmodel AS FItemModel, ";
		sql += " t15.FName AS FUnitName,Convert(decimal(18,4),u1.Fauxqty) AS FQty,u1.FBatchNo AS FBatchNo,t10.FName AS FuserName, ";
		sql += " (CASE t510.FName WHEN '*' THEN '' ELSE t510.FName END)  AS FSPName,dbo.fn_getmatterwidth(u1.FBatchNo) FWidth ";
		sql += " from RemoteServer.AIS2009.dbo.ICStockBill v1  ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0 ";
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Department t4 ON     v1.FDeptID = t4.FItemID   AND t4.FItemID <>0 ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_Stock t7 ON     u1.FDCStockID = t7.FItemID   AND t7.FItemID <>0  ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_User t10 ON     v1.FBillerID = t10.FUserID   AND t10.FUserID <>0  ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t12 ON     u1.FItemID = t12.FItemID   AND t12.FItemID <>0 ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_MeasureUnit t15 ON     u1.FUnitID = t15.FItemID   AND t15.FItemID <>0 ";
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_StockPlace t510 ON     u1.FDCSPID = t510.FSPID   AND t510.FSPID <>0 ";
		sql += " where 1=1 AND  ( v1.FCheckerID>0 and v1.FTranType=2 and (v1.FCancellation = 0)) ";
		// sql += " AND (v1.FDate>='2014-07-01' AND  v1.FDate<'2014-08-01') ";

		String Fdate_begin = request.getParameter("Fdate_begin");
		String Fdate_end = request.getParameter("Fdate_end");
		String FBillNo = request.getParameter("FBillNo");
		String FFullNumber = request.getParameter("FFullNumber");
		String FItemName = request.getParameter("FItemName");
		String FItemModel = request.getParameter("FItemModel");
		String FWidth = request.getParameter("FWidth");
		String FBatchNo = request.getParameter("FBatchNo");
		String FSPName = request.getParameter("FSPName");
		String FDCStockName = request.getParameter("FDCStockName");


		if (StringUtil.isNotEmpty(Fdate_begin) && StringUtil.isNotEmpty(Fdate_end))
		{
			sql += " and v1.Fdate between " + "'" + Fdate_begin + "'" + " and " + "'" + Fdate_end + "'";
		}
		if (StringUtil.isNotEmpty(FBillNo))
		{
			sql += " and v1.FBillNo like " + "'%" + FBillNo + "%'";
		}

		if (StringUtil.isNotEmpty(FFullNumber))
		{
			
			sql += " and t12.FNumber like " + "'%" + FFullNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FItemName))
		{
			
			sql += " and t12.Fname like " + "'%" + FItemName + "%'";
		}
		if (StringUtil.isNotEmpty(FItemModel))
		{
			
			sql += " and t12.Fmodel = " + "'" + FItemModel + "'";
		}
		if (StringUtil.isNotEmpty(FWidth))
		{
			
			sql += " and dbo.fn_getmatterwidth(u1.FBatchNo) = " + "'" + FWidth + "'";
		}

		if (StringUtil.isNotEmpty(FBatchNo))
		{
			
			sql += " and u1.FBatchNo like " + "'%" + FBatchNo + "%'";
		}
		if (StringUtil.isNotEmpty(FDCStockName))
		{
			
			sql += " and t7.FName  = " + "'" + FDCStockName + "'";
		}

		if (StringUtil.isNotEmpty(FSPName))
		{
			
			sql += " and t510.FName = " + "'" + FSPName + "'";
		}

		sql += " order by v1.FInterID,u1.FEntryID ";

		// 排序
		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){

					sql += " order by " + orderField + dataGrid.getOrder();

			}
		}
		
		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());
		} catch (Exception ex)
		{

		} finally
		{

		}

		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		// 数量合计
		BigDecimal sum_qty = new BigDecimal("0.0000");
		for (Map<String, Object> map : maplist)
		{
		   sum_qty = sum_qty.add((BigDecimal) map.get("FQty"));
		}
		dataGrid.setFooter("FUnitName:合计," + "FQty:" + sum_qty);
		TagUtil.datagrid(response, dataGrid);

	}
	
	
	/**
	 * 
	 * 導出
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export")
	public void export(HttpServletRequest request,HttpServletResponse response, DataGrid dataGrid) {
	     
		String sql = " select  Convert(varchar(50),v1.Fdate,23) AS FDate,v1.FBillNo AS FBillNo,t7.FName AS FDCStockName, ";
		sql += " t12.FShortNumber AS FShortNumber,t12.FNumber AS FFullNumber,t12.Fname AS FItemName,t12.Fmodel AS FItemModel, ";
		sql += " t15.FName AS FUnitName,Convert(decimal(18,4),u1.Fauxqty) AS FQty,u1.FBatchNo AS FBatchNo,t10.FName AS FuserName, ";
		sql += " (CASE t510.FName WHEN '*' THEN '' ELSE t510.FName END)  AS FSPName,dbo.fn_getmatterwidth(u1.FBatchNo) FWidth ";
		sql += " from RemoteServer.AIS2009.dbo.ICStockBill v1  ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID   AND u1.FInterID <>0 ";
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_Department t4 ON     v1.FDeptID = t4.FItemID   AND t4.FItemID <>0 ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_Stock t7 ON     u1.FDCStockID = t7.FItemID   AND t7.FItemID <>0  ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_User t10 ON     v1.FBillerID = t10.FUserID   AND t10.FUserID <>0  ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_ICItem t12 ON     u1.FItemID = t12.FItemID   AND t12.FItemID <>0 ";
		sql += " INNER JOIN RemoteServer.AIS2009.dbo.t_MeasureUnit t15 ON     u1.FUnitID = t15.FItemID   AND t15.FItemID <>0 ";
		sql += " LEFT OUTER JOIN RemoteServer.AIS2009.dbo.t_StockPlace t510 ON     u1.FDCSPID = t510.FSPID   AND t510.FSPID <>0 ";
		sql += " where 1=1 AND  ( v1.FCheckerID>0 and v1.FTranType=2 and (v1.FCancellation = 0)) ";
		// sql += " AND (v1.FDate>='2014-07-01' AND  v1.FDate<'2014-08-01') ";

		String Fdate_begin = request.getParameter("Fdate_begin");
		String Fdate_end = request.getParameter("Fdate_end");
		String FBillNo = request.getParameter("FBillNo");
		String FFullNumber = request.getParameter("FFullNumber");
		String FItemName = request.getParameter("FItemName");
		String FItemModel = request.getParameter("FItemModel");
		String FWidth = request.getParameter("FWidth");
		String FBatchNo = request.getParameter("FBatchNo");
		String FSPName = request.getParameter("FSPName");
		String FDCStockName = request.getParameter("FDCStockName");


		if (StringUtil.isNotEmpty(Fdate_begin) && StringUtil.isNotEmpty(Fdate_end))
		{
			sql += " and v1.Fdate between " + "'" + Fdate_begin + "'" + " and " + "'" + Fdate_end + "'";
		}
		if (StringUtil.isNotEmpty(FBillNo))
		{
			sql += " and v1.FBillNo like " + "'%" + FBillNo + "%'";
		}

		if (StringUtil.isNotEmpty(FFullNumber))
		{
			
			sql += " and t12.FNumber like " + "'%" + FFullNumber + "%'";
		}
		if (StringUtil.isNotEmpty(FItemName))
		{
			
			sql += " and t12.Fname like " + "'%" + FItemName + "%'";
		}
		if (StringUtil.isNotEmpty(FItemModel))
		{
			
			sql += " and t12.Fmodel = " + "'" + FItemModel + "'";
		}
		if (StringUtil.isNotEmpty(FWidth))
		{
			
			sql += " and dbo.fn_getmatterwidth(u1.FBatchNo) = " + "'" + FWidth + "'";
		}

		if (StringUtil.isNotEmpty(FBatchNo))
		{
			
			sql += " and u1.FBatchNo like " + "'%" + FBatchNo + "%'";
		}
		if (StringUtil.isNotEmpty(FDCStockName))
		{
			
			sql += " and t7.FName  = " + "'" + FDCStockName + "'";
		}

		if (StringUtil.isNotEmpty(FSPName))
		{
			
			sql += " and t510.FName = " + "'" + FSPName + "'";
		}

		sql += " order by v1.FInterID,u1.FEntryID ";

		// 排序
		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){

					sql += " order by " + orderField + dataGrid.getOrder();

			}
		}
		
		List<Map<String, Object>> maplist = null;

		try
		{
			maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();
			
			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("Fdate");
				arr[1] = (String) map.get("FBillNo");
				arr[2] = (String) map.get("FFullNumber");
				arr[3] = (String) map.get("FItemName");
				arr[4] = (String) map.get("FItemModel");
				arr[5] = (String) map.get("FWidth");
				arr[6] = (String) map.get("FBatchNo");
				arr[7] = (String) map.get("FUnitName");
				arr[8] = String.valueOf((BigDecimal) map.get("FQty"));
				arr[9] =  (String) map.get("FuserName");
				arr[10] =  (String) map.get("FDCStockName");
				arr[11] = (String) map.get("FSPName");  
				list.add(arr);
				 
			}
		
	
	
			String[] columnNames = new String[] { "日期", "单据编号","物料代码","物料名称","厚度","宽度","批号","单位","重量","制单","仓库","仓位"};
	       String titleName = "成品入库.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

	        ExcelUtils.create(request, list, columnNames, titleName, fOut);
			
		} catch (Exception ex)
		{

		}
		
	
	}

}
